from fastapi import APIRouter, Request, Form, Depends, BackgroundTasks
from fastapi.responses import HTMLResponse, FileResponse, RedirectResponse, JSONResponse
from fastapi.templating import Jinja2Templates
from jinja2 import DebugUndefined
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session
from app.models.item_db import Base, Item, MaterialSpecificationsItem, MaterialCountItem, AddressItem, MaterialRequestItem, MaterialItem
from app.models.item import ItemModel, MaterialCountModel, AddressModel, MaterialRequestModel
import datetime
from utils.tooling import create_material_template, remove_file
import logging
logger = logging.getLogger(__name__)

router = APIRouter()
engine = create_engine('sqlite:///material_requisition.db')
# Base.metadata.create_all(bind=engine)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

@router.get("/items/{item_id}")
def read_item(item_id: int, q: str = None):
    return {"item_id": item_id, "q": q}

@router.post("/items/")
def create_item(item: ItemModel):
    return item


# 模板配置
templates = Jinja2Templates(directory="/home/instrument/Documents/material_requisition_repo/app/templates",
                            autoescape=True, auto_reload=True, undefined=DebugUndefined)

# 获取数据库会话
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# 主页，展示所有物品
@router.get("/database/", response_class=HTMLResponse)
async def index(request: Request):
    logger.info("index page")
    db = next(get_db())
    material_spec_items = db.query(MaterialSpecificationsItem).all()
    address_items = db.query(AddressItem).all()
    material_request_items = db.query(MaterialRequestItem).all()
    return templates.TemplateResponse("index.html", {"request": request, 
                                    "material_spec_items": material_spec_items, 
                                    "address_items": address_items,
                                    "material_request_items": material_request_items})
@router.get("/alldata", response_class=JSONResponse)
async def index(request: Request):
    db = next(get_db())
    material_spec_items = db.query(MaterialSpecificationsItem).all()
    address_items = db.query(AddressItem).all()
    material_request_items = db.query(MaterialRequestItem).all()
    return {"material_spec_items": material_spec_items,
            "address_items": address_items,
            "material_request_items": material_request_items}

# 创建物品页面
@router.get("/create/{target}", response_class=HTMLResponse)
async def create(target:str, request: Request):
    db = next(get_db())
    if target == "material":
        logger.info("create material page")
        material_spec_items = db.query(MaterialSpecificationsItem).all() 
        return templates.TemplateResponse("create_material.html", {"request": request,
                                    "material_spec_items": material_spec_items})
    elif target == "address":
        return templates.TemplateResponse("create_address.html", {"request": request})
    elif target == "material_request":
        logger.info("create material request page")
        return templates.TemplateResponse("create_material_request_new.html", 
                                          { "request": request,
                                            "material_request_items": None})
    else:
        return {"message": "Invalid target"}

# 处理创建物品请求
@router.post("/create/material")
async def create_material_item(request: Request, name: str = Form(...), 
                            spec: str = Form(...), unit: str = Form(...), 
                            price_per_unit: float = Form(...)):
    
    db = next(get_db())
    # 检查数据库中是否存在具有相同 name 和 spec 的条目
    existing_item = db.query(MaterialSpecificationsItem).filter(
        MaterialSpecificationsItem.name == name,
        MaterialSpecificationsItem.spec == spec
    ).first()
    if existing_item:
        material_spec_items = db.query(MaterialSpecificationsItem).all() 
        return templates.TemplateResponse("create_material.html", {"request": request,
                                    "material_spec_items": material_spec_items})
    
    new_item = MaterialSpecificationsItem(name=name, spec=spec, unit=unit, 
                                          price_per_unit=price_per_unit)
    db.add(new_item)
    db.commit()
    db.refresh(new_item)   

    material_spec_items = db.query(MaterialSpecificationsItem).all() 
    return templates.TemplateResponse("create_material.html", {"request": request,
                                    "material_spec_items": material_spec_items})

@router.api_route("/edit/material_request/{request_id}", methods=["GET", "POST"])
async def edit_material_request_item(request: Request, request_id: int,
                                     db: Session = Depends(get_db), response_class=HTMLResponse):
    logger.info("edit material request page")
    try:
        res = get_material_from_id(request_id)
        logger.info(res)
        if res is None:
            return {"message": "Invalid request_id"}
    except Exception as e:
        logger.error(f"Error: {e}")
        return {"message": "Invalid request_id"}

    return templates.TemplateResponse("create_material_request_new.html",
                                      { "request": request,
                                        "material_request_items": res})

@router.post("/create/material_request_item")
async def create_material_request_post(request: Request, request_data: MaterialRequestModel,
                                       db: Session = Depends(get_db),response_class=HTMLResponse ):
    material_count_items = []
    total_request_price = 0
    request_id = request_data.request_id

    if db.query(MaterialRequestItem).filter(MaterialRequestItem.request_id == request_id).first():
        logger.info(f"request_id {request_id} already exists, updating...") 
        try:
            new_url = request.url_for('show_material_request', 
                                      material_request_id=request_id)
        except Exception as e:
            logger.error(f"Error creating URL: {e}")

        return RedirectResponse(url=new_url, status_code=303) 

    for material_count in request_data.material_counts:
        material_spec_items = []
        total_count_price = 0
        for spec in material_count.specifications:
            spec_item = db.query(MaterialSpecificationsItem).filter(
                MaterialSpecificationsItem.name == spec.name).first()
            
            if spec_item == None:
                logger.error(f'the material {spec.name} is not in the database')
                material_item_price = 0
                total_count_price += 0
                material_item = MaterialItem(
                    name=spec.name,
                    count=spec.count,
                    spec="",
                    unit="",
                    price_per_unit=0,
                    total_price=0,
                    address=material_count.address,
                    material_request_id=request_data.request_id
                )
            else:
                material_item_price = spec_item.price_per_unit * spec.count
                total_count_price += material_item_price
                material_item = MaterialItem(
                    name=spec.name, 
                    count=spec.count, 
                    spec=spec_item.spec,
                    unit=spec_item.unit, 
                    price_per_unit=spec_item.price_per_unit,
                    total_price=material_item_price,
                    address=material_count.address, 
                    material_request_id=request_data.request_id)
            db.add(material_item)
            material_spec_items.append(material_item)
            
        material_count_item = MaterialCountItem(
            address=material_count.address, total_price=total_count_price, 
            material_request_id=request_data.request_id)

        db.add(material_count_item)
        material_count_items.append(material_count_item)
        total_request_price += total_count_price

    material_request = MaterialRequestItem(
        request_id=request_data.request_id, user=request_data.user, total_price=total_request_price,
        datetime=request_data.datetime)
    db.add(material_request)
    db.commit()
    db.refresh(material_request)

    new_url = request.url_for('show_material_request', 
                                material_request_id=request_id)
    return RedirectResponse(url=new_url, status_code=303) 

@router.post("/save/material_request_item")
async def save_material_request_post(request: Request, request_data: MaterialRequestModel,
                                     db: Session = Depends(get_db), response_class=HTMLResponse):
    material_count_items = []
    total_request_price = 0
    request_id = request_data.request_id
    # if db.query(MaterialRequestItem).filter(
    #     MaterialRequestItem.request_id == request_id).first():

def get_material_from_id(material_request_id):
    db = next(get_db())
    print(material_request_id)
    material_request = db.query(MaterialRequestItem).filter(
        MaterialRequestItem.request_id == material_request_id).first()
    if material_request is None:
        return None
    res = {}
    res['request_id'] = str(material_request.request_id)
    res['user'] = str(material_request.user)
    res['total_price'] = str(material_request.total_price)
    res['datetime'] = datetime.datetime.fromisoformat(material_request.datetime).strftime("%Y%m%d")
    res['addresses'] = []
    for material_count_item in db.query(MaterialCountItem).filter(
        MaterialCountItem.material_request_id == material_request_id).all():

        address = {}
        address['address'] = str(material_count_item.address)

        address['total_price'] = str(material_count_item.total_price)
        address['items'] = [] 
        index = 0
        for material_item in db.query(MaterialItem).filter(
            MaterialItem.material_request_id == material_request_id,
            MaterialItem.address == address['address']).all():            
            if material_item.address == material_count_item.address:
                item = {}
                item["name"] = material_item.name
                item["count"] = str(material_item.count)
                item["spec"] = material_item.spec
                item["unit"] = material_item.unit
                item["price_per_unit"] = str(material_item.price_per_unit)
                item["total_price"] = format(material_item.total_price, ".2f") 

                address['items'].append(item) # 确保这里添加了 item
                index += 1

        res['addresses'].append(address)
    return res

@router.get("/show/material_request/{material_request_id}", 
            response_class=HTMLResponse, name="show_material_request")
async def show_material_request(request: Request, material_request_id: int):
    logger.info(f'come in show_material_request: request_id={material_request_id}')

    res = get_material_from_id(material_request_id)
    return templates.TemplateResponse("show_material_request.html", {"request": request,
                                    "material_request": res})

@router.get("/export_excel/{material_request_id}")
async def export_excel(material_request_id: int, background_tasks: BackgroundTasks):
    
    # 获取物料请求数据
    material_request = get_material_from_id(material_request_id)
    if not material_request:
        return {"message": "Material request not found"}

    # 创建Excel文件
    excel_file_path = f"{material_request['datetime']}办公楼.xlsx"
    try:
        create_material_template(excel_file_path, material_request)
    except Exception as e:
        logger.error(f"Error creating Excel file: {e}")
        return {"message": "Failed to create Excel file"}


    # 添加背景任务以删除文件
    background_tasks.add_task(remove_file, excel_file_path)

    # 发送文件给客户端
    return FileResponse(
        path=excel_file_path,
        filename=f"{material_request['datetime']}办公楼.xlsx",
        media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )


@router.post("/create/address")
async def create_address_item(address: str = Form(...)):
    db = next(get_db())
    new_item = AddressItem(address=address)
    db.add(new_item)
    db.commit()
    db.refresh(new_item)
    return {"message": "Address item created successfully"}

# 编辑物品页面
@router.get("/edit/material/{item_id}", response_class=HTMLResponse)
async def edit(request: Request, item_id: int):
    db = next(get_db())
    try:
        item = db.query(MaterialSpecificationsItem).\
                    filter(MaterialSpecificationsItem.item_id == item_id).first()
        if item is None:
            return {"message": "Item not found"}
        
        material_spec_items = db.query(MaterialSpecificationsItem).all()
    except Exception as e:
        return {"message": "Failed to retrieve item", "error": str(e)}

    return templates.TemplateResponse("update_material.html", {"request": request, 
                        "item": item,"material_spec_items":material_spec_items})

# 更新编辑物品请求
@router.post("/update/material/{item_id}")
async def update_item(request: Request, item_id: int, name: str = Form(...), 
                      spec: str = Form(...), unit: str = Form(),
                      price_per_unit: float = Form(...),
                      db: Session = Depends(get_db) ):
    logger.debug(f"update_item: item_id={item_id}, name={name}, spec={spec}, unit={unit}, price_per_unit={price_per_unit}")

    try:
        item = db.query(MaterialSpecificationsItem).\
                filter(MaterialSpecificationsItem.item_id == item_id).first()
        if item is None:
            return {"message": "Item not found"}

        item.name = name
        item.spec = spec
        item.unit = unit
        item.price_per_unit = price_per_unit

        db.commit()
        db.refresh(item)
    except Exception as e:
        db.rollback()
        return {"message": "Failed to update item", "error": str(e)}

    return RedirectResponse(url="/create/material", status_code=303)

# 删除物品
@router.get("/delete/{item_id}")
async def delete_item(request: Request, item_id: int, db: Session = Depends(get_db)):
    item = db.query(MaterialSpecificationsItem).\
        filter(MaterialSpecificationsItem.item_id == item_id).first()
    if item is None:
        return {"message": "Item not found"}
    db.delete(item)
    db.commit()
    material_spec_items = db.query(MaterialSpecificationsItem).all()
    return templates.TemplateResponse("create_material.html", {"request": request,
                                    "material_spec_items": material_spec_items})